﻿using CallCenter.Data.Base;
using System;
using CallCenter.Data.Models;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CallCenter.Data.BizUtilities
{
    public class CommunicationContacts : BaseUtility
    {
        public CommunicationContacts(DataManager manager)
            : base(manager)
        { }
        public int Insert(int commId, int contactid, DateTime date)
        {
            int nRtn = -1;

            string sql = "Insert INTO ccCommcontact " +
                 "(commId, contactId, date) VALUES(@commId, @contactId, @date)  SELECT @pResult =@@IDENTITY ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 10, commId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 100, contactid);
                this.Factory.AddParameter(cmd, "@date", SqlDbType.Date, 100, date);


                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }


            return nRtn; //retruning the data stored in the table
        }


        public int InsertEmployee(int commId, int empId, DateTime date)
        {
            int nRtn = -1;

         string sql  = "Insert INTO ccCommEmployee " +
                "(commId, employeeId, date) VALUES(@commId, @employeeId, @date)  SELECT @pResult =@@IDENTITY ";



            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 10, commId);
                this.Factory.AddParameter(cmd, "@employeeId", SqlDbType.Int, 100, empId);
                this.Factory.AddParameter(cmd, "@date", SqlDbType.Date, 100, date);


                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
                 
            return nRtn; //retruning the data stored in the table
        }



        public int Update(int id, int commId, int contactid, DateTime date)

        {
            int nRtn = -1;

            string sql = "UPDATE ccCommcontact  SET" +
                 " commId=@commId, contactId=@contactId, date=@date WHERE id = @commId ";
            using (var cmd = this.Factory.GetCommand(sql))
            {

                this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 10, commId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 0, contactid);
                this.Factory.AddParameter(cmd, "@date", SqlDbType.DateTime, 0, date);
                this.Factory.AddParameter(cmd, "@Id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }

            return nRtn; //retruning the data stored in the table
        }

        public DataTable GetAll()
        {
            string sql = "SELECT * FROM ccCommcontact";

            return this.Factory.ExecuteTable(sql);

        }
        public DataTable GetAllGrid(int commId)
        { string sql = " select cmc.*, cc.lastName+', '+cc.firstName as contact from ccCommcontact cmc " +
                                     " INNER JOIN ccContacts cc on cc.id = cmc.contactId WHERE cmc.commId = " + commId;

            return this.Factory.ExecuteTable(sql);

        }
        public DataTable GetContEmail(int ContId)
        {
            string sql = @"select Distinct cc.email, cc.firstName, cc.lastName from ccContacts cc
                WHERE cc.id = " + ContId;
            return this.Factory.ExecuteTable(sql);

        }


        public List<EmailVM> GetContactEmails(int ContId)
        {
            string sql = @"select Distinct cc.email, cc.firstName, cc.lastName from ccContacts cc
                WHERE cc.id = " + ContId;
            return this.Factory.ExecuteObjects<EmailVM>(sql);

        }

        public List<EmailVM> GetCompanyContactEmails(int ContId)
        {
            string sql = @"select Distinct cc.email, cc.firstName, cc.lastName from ccContacts cc
                WHERE cc.id = " + ContId;
            return this.Factory.ExecuteObjects<EmailVM>(sql);

        }




        //public string GetContEmailNames(int ContId)
        //{
        //    SqlCommand cmdSelect = m_Connection.CreateCommand();
        //    cmdSelect.CommandType = CommandType.Text;
        //    cmdSelect.CommandText = @"select Distinct cc.email, cc.firstName, cc.lastName from ccContacts cc
        //        WHERE cc.id = " + ContId;
        //    SqlDataAdapter da = new SqlDataAdapter();
        //    DataTable dt = new DataTable();
        //    da.SelectCommand = cmdSelect;
        //    da.Fill(dt);
        //    string name = "";
        //    for (int j = 0; j < dt.Rows.Count; j++)
        //        name = dt.Rows[0]["firstName"].ToString() + "  " + dt.Rows[0]["lastName"].ToString();

        //    if (dt.Rows.Count == 0)
        //        return "none";
        //    else if (dt.Rows.Count == 1)
        //        return name;
        //    return "";

        //}

        public DataTable GetContacts(int companyId) //Gets all the data form the contacts table
        {
            string sql = "SELECT compC.contactId" +
                 " from ddlCompanyContacts compC " +
                 " inner join ddlProductRequests ct on ct.companyClientId = compC.companyId WHERE compC.companyId = " + companyId;

            return this.Factory.ExecuteTable(sql);
        }

        //public string GetContEmailNames(int ContId)
        //{
        // string sql = @"select Distinct cc.email, cc.firstName, cc.lastName from ccContacts cc
        //        WHERE cc.id = " + ContId;
        //    return this.Factory.ExecuteTable(sql);

        //}
        public DataTable GetContact()
        {
            string sql = " select cmc.*, cc.lastName+', '+cc.firstName as contact from ccCommcontact cmc " +
                                      " INNER JOIN ccContacts cc on cc.id = cmc.contactId ";

            return this.Factory.ExecuteTable(sql);

        }
        public void Delete(int commId) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM ccCommcontact " +
                 " WHERE commId = @commId ";


            using (var cmd = this.Factory.GetCommand(sql))
            {

                this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 4, commId);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }
    

    }

}
